<?php

require_once('functions/pageLoad.php');

$user_session = explode("_", $_SESSION['user']);

$content = '<table width="100%" class="standard">';

$trade_sql = (isset($_GET['trade']) && strlen($_GET['trade']) > 0) ? " AND trades.url = '".mysql_real_escape_string($_GET['trade'])."'" : '' ;
//$area_sql = (isset($_GET['area']) && strlen($_GET['area']) > 0) ? " AND areas2.url = '".mysql_real_escape_string($_GET['area'])."'" : '' ;

/*
if(isset($_GET['postcode']) && strlen($_GET['postcode']) > 0 && isset($_GET['radius']) && strlen($_GET['radius']) > 0){

	$address = urldecode($_GET['postcode'])." UK";
        $ch = curl_init();
        curl_setopt($ch, CURLOPT_URL, "http://maps.google.com/maps/geo?q=".urlencode($address)."&output=csv&oe=utf8&sensor=false&key=".$settings['google_api_key']);
        curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
        $output = curl_exec($ch);
        curl_close($ch);
	$outputArr = explode(",",$output);

if(strlen($outputArr[2]) > 0 && strlen($outputArr[3]) > 0){

$distance_sql_1 = ", ROUND(SQRT(POW((69.1 * (".$outputArr[2]." - areas2.latitude)), 2) + POW((53 * (".$outputArr[3]." - areas2.longitude)), 2)), 1) AS distance";
$distance_sql_2 = " HAVING distance <= ".mysql_real_escape_string($_GET['radius']);

}

}
*/

// get area coords

if(isset($_GET['area']) && strlen($_GET['area']) > 0){

$sql = "SELECT id, longitude, latitude FROM areas WHERE url = '".mysql_real_escape_string($_GET['area'])."'";
$query=mysql_query( $sql ) or die ("Error in query: $sql . " . mysql_error());
$coords_rs=mysql_fetch_assoc($query);

$area_id = $coords_rs['id'];

$distance_sql_1 = ", ROUND(SQRT(POW((69.1 * (".$coords_rs['latitude']." - areas2.latitude)), 2) + POW((53 * (".$coords_rs['longitude']." - areas2.longitude)), 2)), 1) AS distance";
$distance_sql_2 = " HAVING distance <= 20";

}

$sql = "SELECT tradesman.id, username, company_name, areas2.area".$distance_sql_1." FROM tradesman 
	LEFT JOIN tradesman_to_area ON tradesman.id = tradesman_to_area.tradesman_id 
	LEFT JOIN areas ON tradesman_to_area.area_id = areas.id 
	LEFT JOIN tradesman_to_trade ON tradesman.id = tradesman_to_trade.tradesman_id 
	LEFT JOIN trades ON tradesman_to_trade.trade_id = trades.id 
	LEFT JOIN addresses ON tradesman.address_id = addresses.id 
	LEFT JOIN areas AS areas2 ON addresses.area_id = areas2.id 
	WHERE tradesman.active = '1'".$trade_sql.$area_sql." GROUP BY tradesman.id".$distance_sql_2." ORDER BY company_name";
	
	//echo '<!--'.$sql.'-->';
$query=mysql_query( $sql ) or die ("Error in query: $sql . " . mysql_error());
$real_rows = mysql_num_rows($query);

if($real_rows > 0){

$limit = ($_GET['page'] < 2) ? '0,26' : (($_GET['page']-1)*26).',26';

$sql.= " LIMIT ".$limit;
$query=mysql_query( $sql ) or die ("Error in query: $sql . " . mysql_error());

$i = 1;

while($tradesmen_rs=mysql_fetch_assoc($query)){

$average_feedback = tradesman_average_feedback($tradesmen_rs['id']);
$tradesman_link = build_tradesman_link($tradesmen_rs['company_name'], $tradesmen_rs['id']);

$highlight = (is_int($i / 2)) ? ' class="highlight"' : '';
$content.='<tr'.$highlight.'><td class="alignLeft"><a href="'.$tradesman_link.'">'.ucfirst(htmlentities($tradesmen_rs['company_name'])).'</a></td><td>'.$tradesmen_rs['area'].'</td><td style="width: 115px;"><img src="/images/'.$average_feedback.'-star.gif" alt="'.($average_feedback / 2).'/5" /></td></tr>';

$i++;

}

$content.='</table><div id="page_selector">'.pageSelector($real_rows, $_GET['page'], 26).'</div>';

}

else{

$content = '<p>There are currently no tradesmen matching that criteria. <a href="/post-a-job"><strong>post a job</strong></a> now and we will find relevant tradesmen in your area.</p>';

}


// search area

// trades dropdown

$trades_dropdown = '<option value="">All trades</option>';

$sql = "SELECT id, trade, url FROM trades ORDER BY trade";
$query=mysql_query( $sql ) or die ("Error in query: $sql . " . mysql_error());
while($trades_rs=mysql_fetch_assoc($query)){
$active = (isset($_GET['trade']) && $_GET['trade'] == $trades_rs['url']) ? ' selected="selected"' : '' ;
$real_trade = (isset($_GET['trade']) && $_GET['trade'] == $trades_rs['url']) ? htmlentities($trades_rs['trade']) : $real_trade;

$trade_id = (isset($_GET['trade']) && $_GET['trade'] == $trades_rs['url']) ? $trades_rs['id'] : $trade_id;
$trades_dropdown.='<option value="'.$trades_rs['url'].'"'.$active.'>'.htmlentities($trades_rs['trade']).'</option>'."\r\n";
}

// areas dropdown

$areas_dropdown = '<option value="">All areas</option>';

$sql = "SELECT area, url FROM areas ORDER BY area";
$query=mysql_query( $sql ) or die ("Error in query: $sql . " . mysql_error());
while($areas_rs=mysql_fetch_assoc($query)){
$active = (isset($_GET['area']) && $_GET['area'] == $areas_rs['url']) ? ' selected="selected"' : '' ;
$real_area = (isset($_GET['area']) && $_GET['area'] == $areas_rs['url']) ? htmlentities($areas_rs['area']) : $real_area;
$areas_dropdown.='<option value="'.$areas_rs['url'].'"'.$active.'>'.htmlentities($areas_rs['area']).'</option>'."\r\n";
}

// radius dropdown

$i = 10;

while($i <= 100){
$active = ($i == $_GET['radius']) ? ' selected="selected"' : '' ;
$radius_dropdown.='<option value="'.$i.'"'.$active.'>'.$i.' miles</option>';
$i = $i + 10;
}
/*
$rightColumn_content='

<aside id="search_panel">

<h1>Search tradesmen</h1>

<form method="get" action="/tradesmen">

<div class="rowWrapper">
<div class="rowLeft"><label for="trade">Trade:</label></div>
<div class="rowRight"><select name="trade" id="trade">
'.$trades_dropdown.'
</select></div>
</div>

<div class="rowWrapper">
<div class="rowLeft"><label for="area">Area:</label></div>
<div class="rowRight"><select name="area" id="area">
'.$areas_dropdown.'
</select></div>
</div>

<!--<div class="rowWrapper">
<div class="rowLeft"><label for="postcode">Postcode:</label></div>
<div class="rowRight"><input type="text" name="postcode" id="postcode" value="'.urldecode($_GET['postcode']).'" class="textbox" style="width: 60px;" /></div>
</div>

<div class="rowWrapper">
<div class="rowLeft"><label for="radius">Radius:</label></div>
<div class="rowRight"><select name="radius" id="radius">
'.$radius_dropdown.'
</select></div>
</div>-->
<inputt ype="hidden" name="radius" value="20" />

<script type="text/javascript">document.write(\'<\'+\'a onclick="return tradesmen_search();" style="float: right; cursor: pointer; margin-top: 5px;"><\'+\'img src="/images/search_button.gif" alt="Search" /><\'+\'/a>\');</script>
<noscript><input type="image" src="/images/search_button.gif" alt="search" style="float: right; cursor: pointer; margin-top: 5px;" /></noscript>

</form>

</aside>
';
*/

// trade content

if(isset($trade_id)){

	// top content

	if(strlen($real_trade) > 0 && strlen($real_area) > 0){

		$top_trade_content = '
		
		<p><strong>You searched for Local '.$real_trade.' In '.$real_area.'.</strong></p>
		<p>Post your job and our registered '.$real_trade.' that are locally based in and around '.$real_area.' will quote you through our online portal.</p>
		
		';
		
		$page_description = 'Post your job and our registered '.$real_trade.' that are locally based in and around '.$real_area.' will quote you through our online portal.';
		
		switch($real_trade){
			
			case "Electrician Part P":
				$page_description = "Tradesmen online services, we have Part P registered electricians who will be happy to quote on any works within your home.";
			break;
			
			case "Kitchen Fitters":
				$page_description = "We have kitchen fitters registered who are also Gas Safe & Part P registered , they have the  latest designs and work in greater London";
			break;
		
		}
		
		
		
	}
	
	// bottom content
	
	$sql = "SELECT content FROM trade_content WHERE trade_id = '".mysql_real_escape_string($trade_id)."' AND area_id = '".mysql_real_escape_string($area_id)."'";
	$query=mysql_query( $sql ) or die ("Error in query: $sql . " . mysql_error());
	$trade_content=mysql_fetch_assoc($query);
	
	if(strlen($trade_content['content']) > 0){
		$bottom_trade_content = $trade_content['content'];
	}
	
	else{
	
		$sql = "SELECT trade_content FROM trades WHERE id = '".mysql_real_escape_string($trade_id)."'";
		$query=mysql_query( $sql ) or die ("Error in query: $sql . " . mysql_error());
		$trade_content=mysql_fetch_assoc($query);
	
		if(strlen($trade_content['trade_content']) > 0){
		$bottom_trade_content = $trade_content['trade_content'];
		}
	
	}
	

}

// page header & title


$big_page_header = $page_title = (strlen($_GET['trade']) > 0 && strlen($_GET['area']) > 0) ?  $real_trade.' '.$real_area.', '.$real_trade : $big_page_header ;

$big_page_header = $page_title = (strlen($_GET['trade']) == 0 && strlen($_GET['area']) > 0) ?  'Tradesmen in '.$real_area : $big_page_header ;

$big_page_header = $page_title = (strlen($_GET['trade']) > 0 && strlen($_GET['area']) == 0) ?  $real_trade : $big_page_header ;

$big_page_header = $page_title = (strlen($_GET['trade']) == 0 && strlen($_GET['area']) == 0) ?  'Tradesmen' : $big_page_header ;

if(isset($_REQUEST['trade'])){
 switch($_REQUEST['trade']){
  case 'plumber':
   $page_title='Local Plumbers UK Plumbers Directory to Find Plumbers in Manchester';
   $page_description='Plumbers in UK- Find plumbers & best plumbers quote in manchester, electrical & plumber directory in southampton UK';
   $page_keywords='Plumbers in UK, plumbers quotes in manchester, plumbers in southampton, electrical plumber directory';
  break;
  case 'electrician':
   $page_title='Local Electrician UK Electrician in London Find Electrician in Manchester';
   $page_description='Electrician in UK- Find Electrician & best Electrician quote in manchester, electrical & plumber directory in southampton UK';
   $page_keywords='Electrician in UK, Electrician quotes in manchester, Electrician in southampton, electrical plumber directory';
  break;
  case 'interior-designer':
   $page_title='Local Interior Designer UK Interior Designer in Dartford Find Interior Designer in Southampton';
   $page_description='Interior Designer in UK- Find Interior Designer & best Interior Designer quote in Southampton, electrical & plumber directory in southampton UK';
   $page_keywords='Interior Designer in UK, Interior Designer quotes in manchester, Interior Designer in southampton, electrical plumber directory';
  break;
 }
}

include('includes/meta.php');
include('includes/header.php');
include('includes/navigation.php');

?>

<h1><?php echo $big_page_header; ?></h1>

<?php echo $top_trade_content; ?>

<div class="yellow_box">
<p><strong>Are you a tradesman? Want to join our team?</strong> <a href="/register-my-business"><strong>Register your business for FREE</strong></a> and receive job requests for your companies trades in your area.</p>
</div>

<?php echo $content; ?>

<?php echo $bottom_trade_content; ?>

<?php 

include('includes/rightColumn.php');
include('includes/footer.php');

?>